package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IPersonExtSubmitDao;
import com.boss.salary.entity.AgencyVerify;
import com.boss.salary.entity.PersonExt;
import com.boss.salary.entity.SalaryBankPayBack;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.InvocationTargetException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class PersonExtSubmitDaoImpl  extends BaseDaoImpl implements IPersonExtSubmitDao {
    @Override
    public void batchSavePersonExtSubmit(List<PersonExt> personExts,Integer isThirteen) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {

        StringBuilder sql = new StringBuilder();

        sql.append(" merge into ").append(TableName.PERSON_EXT_SUBMIT)
                .append(" a using " )
                .append("(select ? id, ?  create_time, ?  update_time, ?  person_id, ?  person_address, ?  agency_id, ?  dept_id, ?  agency_code, ?  iden_no, ? state, ?  enter_agency_date, ? ")
                .append("retire_date, ?  person_type_code, ?  person_ide_code, ?  end_date, ?  dead_date, ?  remarks, ?  sala_card_no, ? ")
                .append("sala_card_bank, ?  gov_cred_card_no, ?  gov_cred_card_bank, ?  start_date, ?  merit_pay_code, ? ")
                .append("sch_rec_code, ?  pos_code, ?  gr_code, ?  tec_gr_code, ?  is_auth, ?  sala_gr_code, ?  person_state, ? ")
                .append("change_state, ?  verify_state, ?  per_source_code, ?  is_special_post, ?  is_insurance, ?  is_uni_sala, ?  is_stop_salary, ?  is_deleted, ? mof_div_code, ?  ")
                .append(" person_type_id, ?  post_id, ?  grade_id, ?  level_id, ? person_name, ? show_order, ? phone, ? personnel_code, ? func_id, ? func_name, ? func_code, ? is_thirteen  from dual) b")
                .append(" on (a.person_id=b.person_id and a.is_thirteen=b.is_thirteen ) " )
                .append(" WHEN MATCHED THEN " )
                .append("  UPDATE SET  a.create_time=b.create_time, a.update_time=b.update_time, a.agency_id=b.agency_id, a.mof_div_code=b.mof_div_code, " )
                .append(" a.person_name=b.person_name,a.func_code=b.func_code,a.iden_no=b.iden_no,a.sala_card_bank=b.sala_card_bank,a.sala_card_no=b.sala_card_no,")
                .append(" a.merit_pay_code=b.merit_pay_code,a.dept_id=b.dept_id,a.agency_code=b.agency_code,a.state=b.state,a.person_state=b.person_state,")
                .append(" a.person_type_id=b.person_type_id,a.post_id=b.post_id,a.grade_id=b.grade_id,a.level_id=b.level_id,a.show_order=b.show_order,")
                .append(" a.phone=b.phone,a.personnel_code=b.personnel_code,a.func_id=b.func_id,a.func_name=b.func_name")
                .append( " WHEN NOT MATCHED THEN " )
                .append( "   INSERT " )
                .append("(id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, iden_no,state, enter_agency_date,")
                .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
                .append("change_state, verify_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary, is_deleted,mof_div_code, ")
                .append(" person_type_id, post_id, grade_id, level_id,person_name,show_order,phone,personnel_code,func_id,func_name,func_code,is_thirteen )")
                .append( "   VALUES " )
                .append("(b.id, b.create_time, b.update_time, b.person_id, b.person_address, b.agency_id, b.dept_id, b.agency_code, b.iden_no,b.state, b.enter_agency_date,")
                .append("b.retire_date, b.person_type_code, b.person_ide_code, b.end_date, b.dead_date, b.remarks, b.sala_card_no,")
                .append("b.sala_card_bank, b.gov_cred_card_no, b.gov_cred_card_bank, b.start_date, b.merit_pay_code,")
                .append("b.sch_rec_code, b.pos_code, b.gr_code, b.tec_gr_code, b.is_auth, b.sala_gr_code, b.person_state,")
                .append("b.change_state, b.verify_state, b.per_source_code, b.is_special_post, b.is_insurance, b.is_uni_sala, b.is_stop_salary, b.is_deleted,b.mof_div_code, ")
                .append("b.person_type_id, b.post_id, b.grade_id, b.level_id,b.person_name,b.show_order,b.phone,b.personnel_code,b.func_id,b.func_name,b.func_code,b.is_thirteen )");

//        sql.append("insert into ").append(TableName.PERSON_EXT_SUBMIT);
//        sql.append("(id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, iden_no,state, enter_agency_date,")
//                .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
//                .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
//                .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
//                .append("change_state, verify_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary, is_deleted,mof_div_code, ")
//                .append(" person_type_id, post_id, grade_id, level_id,person_name,show_order,phone,personnel_code,func_id,func_name,func_code,is_thirteen )");
//        sql.append("values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return personExts.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                PersonExt personExt = personExts.get(i);
                ps.setObject(1,SysUtils.generateGuid());
                ps.setObject(2, personExt.getCreateTime());
                ps.setObject(3, personExt.getUpdateTime());
                ps.setObject(4, personExt.getPersonId());
                ps.setObject(5,personExt.getPersonAddress());
                ps.setObject(6, personExt.getAgencyId());
                ps.setObject(7, personExt.getDeptId());
                ps.setObject(8, personExt.getAgencyCode());
                ps.setObject(9,personExt.getIdenNo());
                ps.setObject(10,personExt.getState());
                ps.setObject(11,personExt.getEnterAgencyDate());
                ps.setObject(12, personExt.getRetireDate());
                ps.setObject(13, personExt.getPersonTypeCode());
                ps.setObject(14,personExt.getPersonIdeCode());
                ps.setObject(15, personExt.getEndDate());
                ps.setObject(16, personExt.getDeadDate());
                ps.setObject(17,personExt.getRemarks());
                ps.setObject(18,personExt.getSalaCardNo());
                ps.setObject(19, personExt.getSalaCardBank());
                ps.setObject(20, personExt.getGovCredCardNo());
                ps.setObject(21,personExt.getGovCredCardBank());
                ps.setObject(22, personExt.getStartDate());
                ps.setObject(23, personExt.getMeritPayCode());
                ps.setObject(24,personExt.getSchRecCode());
                ps.setObject(25, personExt.getPosCode());
                ps.setObject(26, personExt.getGrCode());
                ps.setObject(27, personExt.getTecGrCode());
                ps.setObject(28,personExt.getIsAuth());
                ps.setObject(29, personExt.getSalaGrCode());
                ps.setObject(30, personExt.getPersonState());
                ps.setObject(31, personExt.getChangeState());
                ps.setObject(32,personExt.getVerifyState());
                ps.setObject(33, personExt.getPerSourceCode());
                ps.setObject(34, personExt.getIsSpecialPost());
                ps.setObject(35, personExt.getIsInsurance());
                ps.setObject(36,personExt.getIsUniSala());
                ps.setObject(37, personExt.getIsStopSalary());
                ps.setObject(38, personExt.getIsDeleted());
                ps.setObject(39,personExt.getMofDivCode());
                ps.setObject(40,personExt.getPersonTypeId());
                ps.setObject(41, personExt.getPostId());
                ps.setObject(42, personExt.getGradeId());
                ps.setObject(43, personExt.getLevelId());
                ps.setObject(44,personExt.getPersonName());
                ps.setObject(45,personExt.getShowOrder());
                ps.setObject(46,personExt.getPhone());
                ps.setObject(47,personExt.getPersonnelCode());
                ps.setObject(48,personExt.getFuncId());
                ps.setObject(49,personExt.getFuncName());
                ps.setObject(50,personExt.getFuncCode());
                ps.setObject(51,isThirteen);

            }
        } );
    }

    @Override
    public void batchPhysicalDeletePersonExtSubmit(String province, List<String> agencyIds, Integer IsThirteen) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ").append(TableName.PERSON_EXT_SUBMIT)
                .append(" where ").append(" mof_div_code = ? ");
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
        }
             sb.append(" and ").append(" Is_Thirteen = ? ");
        super.getjdbcTemplate().update(sb.toString(), province,IsThirteen);
        //return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyInfo.class),userDTO.getProvince())
    }

    @Override
    public void updateSalaCardNoByIdenNo(SalaryBankPayBack salaryBankPayBack) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.PERSON_EXT_SUBMIT).append(" pe ")
                .append(" set pe.sala_card_no = ?,pe.person_name=?,pe.iden_no=? ").append(" where pe.person_id = ? ");
        Object[] params = new Object[]{
                salaryBankPayBack.getNewSalaCardNo(),salaryBankPayBack.getNewPersonName(),
                salaryBankPayBack.getNewIdenNo(), salaryBankPayBack.getPersonId()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }
//通过异常卡号的身份证号去人员基本信息上报表查信息
    @Override
    public List<Map<String, Object>> UnitOfAbnormal(List<SalaryBankPayBack> salaryBankPayBacks) {
        List<String> paramList = new ArrayList<>();
        for(SalaryBankPayBack salaryBankPayBack:salaryBankPayBacks) {
            paramList.add(salaryBankPayBack.getPersonId());
        }
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" agency_id as \"agency_id\",COUNT(*) AS \"cn\" ")
                .append(" from ");
                sql.append(TableName.PERSON_EXT_SUBMIT)
                .append(" where");
                sql.append(SysUtils.inSql("person_id",paramList))
                .append(" group by agency_id");

        return super.getJdbcTemplate().queryForList(sql.toString());


    }

    @Override
    public void updatePersonExtSubmitCode(List<AgencyVerify> updateList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.PERSON_EXT_SUBMIT);
        sql.append(" set  agency_code=?   where agency_id=?");
        List<Object[]> paramsList = new ArrayList<>(updateList.size());
        for (AgencyVerify personExtSubmit : updateList) {
            Object[] params = new Object[]{
                    personExtSubmit.getAgencyCode(),personExtSubmit.getAgencyId()
            };
            paramsList.add(params);
        }
        super.getJdbcTemplate().batchUpdate(sql.toString(),  new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }
}
